﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Net.Mail;
using System.Data;


namespace EWDTAssignment.App_Code
{
    public class Account
    {
        private int accountID;

        public int AccountID
        {
            get { return accountID; }
            set { accountID = value; }
        }
        private string username;

        public string Username
        {
            get { return username; }
            set { username = value; }
        }
        private string password;

        public string Password
        {
            get { return password; }
            set { password = value; }
        }
        private string question;

        public string Question
        {
            get { return question; }
            set { question = value; }
        }
        private string answer;

        public string Answer
        {
            get { return answer; }
            set { answer = value; }
        }
        private string type;

        public string Type
        {
            get { return type; }
            set { type = value; }
        }

        /*
        public static bool checkAvailability(string name)
        //to check whether the username is available or not. If it is available return true, else return false
        {
            bool result = true;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["eShopDB"].ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand comm = conn.CreateCommand())
                    {
                        comm.CommandText = "select * from Account where Username=@username";
                        comm.Parameters.AddWithValue("@username", name);
                        SqlDataReader dr = comm.ExecuteReader();
                        while(dr.Read())
                        {
                            result = false;
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }    
            }
            return result;
        }
        */
        /*
        public static bool createCustomerAccount(Account account)
        //to insert a new account. If insert success return true, else return false
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["eShopDB"].ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand comm = conn.CreateCommand())
                    {
                        comm.CommandText = "insert into Account(Username,Password,Question,Answer,Type,) values (@username,@password,@question,@answer,'Customer')";
                        comm.Parameters.AddWithValue("@username",account.Username);
                        comm.Parameters.AddWithValue("@password", account.Password);
                        comm.Parameters.AddWithValue("@question",account.Question);
                        comm.Parameters.AddWithValue("@answer", account.Answer);
                        int i = (int)comm.ExecuteNonQuery();
                        if (i != 0)
                        {
                            result = true;
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return result;
        }
        */
        public static Account Login(Account account)
        //to log in the website. If the user got correct username and password, then they can retrieve the accountID, else the accountID is null.
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["eShopDB"].ConnectionString))
            {
                try
                {
            
                    conn.Open();
                    using (SqlCommand comm = conn.CreateCommand())
                    {
                        comm.CommandText = "select * from Account where Username=@username and Password=@password";
                        comm.Parameters.AddWithValue("@username", account.Username);
                        comm.Parameters.AddWithValue("@password", account.Password);
                        SqlDataReader dr = comm.ExecuteReader();
                        while (dr.Read())
                        {
                            account.AccountID = (int)dr["AccountID"];
                            account.Type = dr["Type"].ToString();
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return account;
        }

        /*
        //forget password (To check the username and First_Name, if match, then send to his email)
        public static void forgetPassword(string username,string question,string answer)
        {
            string newPassowrd=CreateRandomPassword(8);
            
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["eShopDB"].ConnectionString))
            {
                Account newAccount = new Account();
                
                try
                {
                    conn.Open();
                    using (SqlCommand comm = conn.CreateCommand())
                    {
                        comm.CommandText = "select * from Account set Password=@password where Username=@username and Question=@question and Answer=@answer";
                        comm.Parameters.AddWithValue("@username", username);
                        comm.Parameters.AddWithValue("@password",newPassowrd);
                        comm.Parameters.AddWithValue("@question", question);
                        comm.Parameters.AddWithValue("@answer", answer);
                        int i = (int)comm.ExecuteNonQuery();
                        if (i != 0)
                        {
                            string emailAddress = "zhengtianyou1989@hotmail.com";
                            string title = "recover password";
                            string content = "Username: " + username + "\nPassword: " + newPassowrd;
                            Email.sentEmail(emailAddress, title, content);
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
        */

        /*
        //change password
        public static bool changePassword(Account account, string newPassword)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["eShopDB"].ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand comm = conn.CreateCommand())
                    {
                        comm.CommandText = "Update Account set Password=@newPassword where Username=@username and Password=@password";
                        comm.Parameters.AddWithValue("@username", account.Username);
                        comm.Parameters.AddWithValue("@password", account.Password);
                        comm.Parameters.AddWithValue("@newPassword", newPassword);
                        int i = (int)comm.ExecuteNonQuery();
                        if (i != 0)
                        {
                            result = true;
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return result;
        }
        */
        private static string CreateRandomPassword(int passwordLength)
        {
            string allowedChars = "abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ0123456789!@$?_-";
            char[] chars = new char[passwordLength];
            Random rd = new Random();

            for (int i = 0; i < passwordLength; i++)
            {
                chars[i] = allowedChars[rd.Next(0, allowedChars.Length)];
            }

            return new string(chars);
        } 

        public static int checkAvailability1(string name)
        {
            string connectionString = "select count(*) from Account where Username=@username";
            int a = (int)CRUD.ExecuteScalar(connectionString, new SqlParameter("@username", name));
            return a;
        }
        public static int createCustomerAccount1(Account account)
        {
            string connectionString = "insert into Account(Username,Password,Type,Question,Answer) values (@username,@password,@Type,@question,@answer)";
            int a = 0;
          
            a=CRUD.ExecuteNonQuery(connectionString, new SqlParameter("@username", account.Username), new SqlParameter("@password", account.Password), new SqlParameter("@Type", account.Type), new SqlParameter("@question", account.Question), new SqlParameter("@answer", account.Answer));
            return a;
        }
        public static void forgetPassword(string username, string question, string answer,string email)
        {
            string newPassword=CreateRandomPassword(8);
            string emailAddress = email;
            string connectionString = "update Account set Password=@password where Username=@username and Question=@question and Answer=@answer";
            int a = 0;
            a = CRUD.ExecuteNonQuery(connectionString, new SqlParameter("@username", username), new SqlParameter("@password", newPassword), new SqlParameter("@question", question), new SqlParameter("@answer", answer));
            if(a!=0)
            {
                //string connectionString2 = "select Email from Customer where AccountID=(select * from Account where Username=@username)";
                //DataTable dt = CRUD.ExecuteReader(connectionString2, new SqlParameter("@username", username));
               // string emailAddress = dt.Rows[0].ToString();
                string title = "recover password";
                string content = "Username: " + username + "\nPassword: " + newPassword;
                Email.sentEmail(emailAddress, title, content);
            }
        }
        public static int changePassword(Account account, string newPassword)
        {
            string connectionString = "Update Account set Password=@newPassword where Username=@username and Password=@password";
            int a = CRUD.ExecuteNonQuery(connectionString, new SqlParameter("@username", account.Username), new SqlParameter("@password", account.Password), new SqlParameter("@newPassword", newPassword));
            return a;
        }
        public static string welcome(int accountID)
        {
            string connectionString = "select Username from Account where AccountID=@accountID";
            DataTable dt = new DataTable();
            dt = CRUD.ExecuteReader(connectionString,new SqlParameter("@accountID",accountID));
            string welcome = dt.Rows[0][0].ToString();
            return welcome;
        }
    }
}